﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_HrmPositions
    {
        public D_HrmPositions()
        {

        }

        public DataTable GetPosition(string strPosSeq)
        {
            string strSQL;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@pos_seq", strPosSeq);
                strSQL = @"
select * from hrm_mp_positions where pos_seq = @pos_seq
";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                return dtb;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        
        public DataSet GetPositionUpper(Nullable<int> EmpSequence, Nullable<int> PosSequence, string SearchText)
        {
            string strSQL, strView;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@PosSequence", PosSequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@SearchText", SearchText);
                dbManager.AddParameters(4, "@strView",
                    "(SELECT e0.emp_seq AS emp_seq_0, e0.pos_seq AS pos_seq_0, e1.emp_seq AS emp_seq_1"
                       + ", e1.pos_seq AS pos_seq_1, e2.emp_seq AS emp_seq_2, e2.pos_seq AS pos_seq_2"
                       + ", e3.emp_seq AS emp_seq_3, e3.pos_seq AS pos_seq_3"
                       + " FROM hrm_ps_employees e0"
                       + " LEFT JOIN hrm_mp_positions p0 ON e0.pos_seq = p0.pos_seq"
                       + " LEFT JOIN ( SELECT ee.emp_seq, ee.pos_seq, ee.emp_status_seq"
      			                    + " FROM hrm_ps_employees ee"
  			                        + " LEFT JOIN hrm_ps_employee_statuses s ON ee.emp_status_seq = s.emp_status_seq"
 			                        + " WHERE s.active <> 0"
                                    + ") e1 ON e1.pos_seq = p0.pos_working_upper_seq"
                       + " LEFT JOIN hrm_mp_positions p1 ON e1.pos_seq = p1.pos_seq"
                       + " LEFT JOIN ( SELECT ee.emp_seq, ee.pos_seq, ee.emp_status_seq"
   			                    + " FROM hrm_ps_employees ee"
   			                    + " LEFT JOIN hrm_ps_employee_statuses s ON ee.emp_status_seq = s.emp_status_seq"
  			                    + " WHERE s.active <> 0"
                                + ") e2 ON e2.pos_seq = p1.pos_working_upper_seq"
                       + " LEFT JOIN hrm_mp_positions p2 ON e2.pos_seq = p2.pos_seq"
                       + " LEFT JOIN ( SELECT ee.emp_seq, ee.pos_seq, ee.emp_status_seq"
   			                    + " FROM hrm_ps_employees ee"
   			                    + " LEFT JOIN hrm_ps_employee_statuses s ON ee.emp_status_seq = s.emp_status_seq"
  			                    + " WHERE s.active <> 0"
                                + ") e3 ON e3.pos_seq = p2.pos_working_upper_seq)");
                strSQL = "SELECT ue.EMP_SEQ AS UPPER_EMP_SEQ"
                        + ", CONCAT(np.PREFIX_NAME, ue.FIRST_NAME, '  ', ue.LAST_NAME) AS FULL_NAME"
                    + " FROM HRM_PS_EMPLOYEES e"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS up ON p.POS_WORKING_UPPER_SEQ = up.POS_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES ue ON up.POS_SEQ = ue.POS_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON ue.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " WHERE e.EMP_SEQ = @EmpSequence";
                //strSQL = "SELECT tt.* "
                //    + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS FULL_NAME"
                //    + ", ex.EXEC_POS_NAME"
                //    + ", CASE WHEN e.WORKING_JOB_SITE_SEQ IS NOT NULL THEN wj.JOB_SITE_NAME"
                //        + " ELSE j.JOB_SITE_NAME END AS JOB_SITE_NAME"
                //    + " FROM("
                //        + " SELECT t1.EMP_SEQ_0 AS EMP_SEQ, t1.EMP_SEQ
                //        + " WHERE t1.EMP_SEQ_1 IS NOT NULL"
                //        + " UNION ALL"
                //        + " SELECT t2.EMP_SEQ_0 AS EMP_SEQ, t2.EMP_SEQ_2 AS UPPER_EMP_SEQ, 2 AS LEVEL_NO"
                //        + " FROM @strView t2"
                //        + " WHERE t2.EMP_SEQ_2 IS NOT NULL)"
                //        + " UNION ALL "
                //        + " SELECT t3.EMP_SEQ_0 AS EMP_SEQ, t3.EMP_SEQ_3 AS UPPER_EMP_SEQ, 3 AS LEVEL_NO"
                //        + " FROM @strView t3"
                //        + " WHERE t3.EMP_SEQ_3 IS NOT NULL"
                //    + ")tt"
                //    + " INNER JOIN HRM_PS_EMPLOYEES e ON tt.UPPER_EMP_SEQ = e.EMP_SEQ"
                //    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                //    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                //    + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                //    + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
                //    + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                //    + " WHERE e.RECORD_STATUS = @ActiveRecord"
                //    + (EmpSequence.HasValue ? " AND tt.EMP_SEQ = @EmpSequence" : "")
                //    //+ (PosSequence.HasValue ? " AND tt.EMP_SEQ = @PosSequence" : "")
                //    + " ORDER BY tt.LEVEL_NO";

                

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
